﻿using Ext.Net;
using System;
using System.Data;

public partial class InsAverageMark : ExtBaseRpt
{
    string mid;
    protected void Page_Load(object sender, EventArgs e)
    {
        mid = Request.Params["mid"];
        if (!X.IsAjaxRequest)
        { }
    }

    //拼接in（）条件查询
    protected string StrSplit(string[] str)
    {
        //字符串处理('M00001','M00002')
        if (str.Length > 0)
        {
            string rslt = "(";
            foreach (string val in str)
            {
                if (val != "")
                    rslt += "'" + val + "',";
            }
            return rslt.TrimEnd(',') + ")";
        }
        else return "";
    }

    //查询省份数据 
    [DirectMethod(Namespace = "CompanyX")]
    public DataTable GetProvinceData(string JsonParams)
    {
        try
        {
            string selQuery = string.Format(@"SELECT DISTINCT BigAreaName From V_ShopList WHERE CompanyID='KM' AND ISNULL(BigAreaName,'') <>'' ");
            DataSet ds = mydb.DM_GetDateSet(selQuery);
            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            ShowBox(ex.Message);
        }
        return null;
    }

    //查询店经理数据
    [DirectMethod(Namespace = "CompanyX")]
    public DataTable GetShopManagerData(string JsonParams)
    {
        try
        {
            //反序列化为ShopImg对象
            var model = JSON.Deserialize<QueryReqModel>(JsonParams);
            string selQuery = string.Format(@"SELECT DISTINCT tif_shopManager AS ShopManager FROM t_inspection_finishMaster WHERE 1=1 AND isRemove=0 AND tif_progress = 1 AND tif_checkDate>='{0}' AND tif_checkDate<='{1}' ", model.StartTime, model.EndTime);
            DataSet ds = mydb.DM_GetDateSet(selQuery);
            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            ShowBox(ex.Message);
        }
        return null;
    }

    //查询所有的平均数
    [DirectMethod(Namespace = "CompanyX", Timeout = 90000)]
    public QueryResDataModel QueryAveRun(string JsonParams)
    {
        QueryResDataModel ResMod = new QueryResDataModel();
        try
        {
            //反序列化为ShopImg对象 
            var model = JSON.Deserialize<QueryReqModel>(JsonParams);
            ResMod.State = 1;
            ResMod.AllAve = AllAve(model.StartTime, model.EndTime);
            ResMod.CityAve = CityAve(model.StartTime, model.EndTime, model.Province);
            ResMod.ManagerAve = ManagerAve(model.StartTime, model.EndTime, model.ShopManager);
            ResMod.MapAve = MapAve(model.StartTime, model.EndTime);
            return ResMod;
        }
        catch (Exception ex)
        {
            ShowBox(ex.Message);
            ResMod.State = 0;
            ResMod.CityAve = new DataTable();
            ResMod.ManagerAve = new DataTable();
            ResMod.MapAve = new DataTable();
            return ResMod;
        }
    }

    //查询城市的平均数
    [DirectMethod(Namespace = "CompanyX", Timeout = 900000)]
    public QueryResDataModel ChangeProvinceRun(string JsonParams)
    {
        QueryResDataModel ResMod = new QueryResDataModel();
        try
        {
            //反序列化为ShopImg对象
            var model = JSON.Deserialize<QueryReqModel>(JsonParams);
            ResMod.State = 1;
            ResMod.CityAve = CityAve(model.StartTime, model.EndTime, model.Province);
            return ResMod;
        }
        catch (Exception ex)
        {
            ShowBox(ex.Message);
            ResMod.State = 0;
            ResMod.CityAve = new DataTable();
            return ResMod;
        }
    }

    //查询店经理的平均数
    [DirectMethod(Namespace = "CompanyX", Timeout = 900000)]
    public QueryResDataModel ChangeShopManagerRun(string JsonParams)
    {
        QueryResDataModel ResMod = new QueryResDataModel();
        try
        {
            //反序列化为ShopImg对象
            var model = JSON.Deserialize<QueryReqModel>(JsonParams);
            ResMod.State = 1;
            ResMod.ManagerAve = ManagerAve(model.StartTime, model.EndTime, model.ShopManager);
            return ResMod;
        }
        catch (Exception ex)
        {
            ShowBox(ex.Message);
            ResMod.State = 0;
            ResMod.ManagerAve = new DataTable();
            return ResMod;
        }
    }

    //导出城市的平均分数据
    [DirectMethod(Namespace = "CompanyX", Timeout = 900000)]
    public OutExcelPath OutCityAveRun(string JsonParams)
    {
        OutExcelPath OutExcel = new OutExcelPath();
        try
        {
            //反序列化为ShopImg对象
            var model = JSON.Deserialize<QueryReqModel>(JsonParams);
            string BigAreaName = string.Empty;
            if (!string.IsNullOrEmpty(model.Province))
            {
                BigAreaName = string.Format(@" AND sl.BigAreaName='{0}' ", model.Province);
            }
            string selQuery = string.Format(@"SELECT sl.AreaName AS 城市,COUNT(1) AS 店铺数量,cast(SUM(fm.new_tif_score)/COUNT(1) AS NUMERIC(10,1)) AS 平均数 FROM V_t_inspection_finishMaster AS fm
INNER JOIN V_ShopList AS sl
ON sl.shopcode=fm.tif_shopCode
WHERE 1=1 
AND fm.tif_progress = 1 
AND sl.CompanyID='KM'
AND fm.tif_checkDate>='{0}'
AND fm.tif_checkDate<='{1}' 
{2}
GROUP BY sl.AreaName 
ORDER BY 平均数 DESC ", model.StartTime, model.EndTime, BigAreaName);

            DataSet ds = mydb.DM_GetDateSet(selQuery);
            if (ds.Tables[0].Rows.Count > 0)
            {
                string path = NpoiHelper.ExportByWeb(mid, ds, "城市平均分数");
                OutExcel.State = 1;
                OutExcel.Msg = "";
                OutExcel.Path = path;
            }
            else
            {
                OutExcel.State = 0;
                OutExcel.Msg = "没有查找到对应的数据";
                OutExcel.Path = "";
            }
        }
        catch (Exception ex)
        {
            OutExcel.State = 0;
            OutExcel.Msg = ex.Message;
            OutExcel.Path = "";
        }
        return OutExcel;
    }

    //导出店经理平均分数据
    [DirectMethod(Namespace = "CompanyX", Timeout = 900000)]
    public OutExcelPath OutManagerAveRun(string JsonParams)
    {
        OutExcelPath OutExcel = new OutExcelPath();
        try
        {
            //反序列化为ShopImg对象
            var model = JSON.Deserialize<QueryReqModel>(JsonParams);
            string ManagerName = string.Empty;
            string ShopManagerItem = StrSplit(model.ShopManager);

            if (!string.IsNullOrEmpty(ShopManagerItem))
            {
                ManagerName = string.Format(@" AND fm.tif_shopManager IN {0} ", ShopManagerItem);
            }
            string selQuery = string.Format(@"SELECT fm.tif_shopManager AS 店经理,COUNT(1) AS 店铺数量,cast(SUM(fm.new_tif_score)/COUNT(1) AS NUMERIC(10,1)) AS 平均数 FROM V_t_inspection_finishMaster AS fm
WHERE 1=1 
AND fm.tif_progress = 1 
AND fm.tif_checkDate>='{0}'
AND fm.tif_checkDate<='{1}'
{2}
GROUP BY fm.tif_shopManager 
ORDER BY 平均数 DESC ", model.StartTime, model.EndTime, ManagerName);

            DataSet ds = mydb.DM_GetDateSet(selQuery);
            if (ds.Tables[0].Rows.Count > 0)
            {
                string path = NpoiHelper.ExportByWeb(mid, ds, "店经理平均分数");
                OutExcel.State = 1;
                OutExcel.Msg = "";
                OutExcel.Path = path;
            }
            else
            {
                OutExcel.State = 0;
                OutExcel.Msg = "没有查找到对应的数据";
                OutExcel.Path = "";
            }
        }
        catch (Exception ex)
        {
            OutExcel.State = 0;
            OutExcel.Msg = ex.Message;
            OutExcel.Path = "";
        }
        return OutExcel;
    }

    //获取全国平均分
    public decimal AllAve(DateTime StartTime, DateTime EndTime)
    {
        try
        {
            string selQuery = string.Format(@"SELECT cast(SUM(new_tif_score)/COUNT(1) AS NUMERIC(10,1)) AS AllAve FROM V_t_inspection_finishMaster WHERE 1=1 AND tif_progress = 1 AND tif_checkDate>='{0}' AND tif_checkDate<='{1}' ", StartTime, EndTime);
            object obj = mydb.DM_GetExecuteScalarObj(selQuery);
            decimal num = 0;
            if (obj != DBNull.Value)
            {
                num = Convert.ToDecimal(obj);
            }
            return num;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    //获取城市的平均数据
    public DataTable CityAve(DateTime StartTime, DateTime EndTime, string Province)
    {
        try
        {
            string BigAreaName = string.Empty;
            if (!string.IsNullOrEmpty(Province))
            {
                BigAreaName = string.Format(@" AND sl.BigAreaName='{0}' ", Province);
            }
            string selQuery = string.Format(@"SELECT sl.AreaName AS name,COUNT(1) AS num,cast(SUM(fm.new_tif_score)/COUNT(1) AS NUMERIC(10,1)) AS ave FROM V_t_inspection_finishMaster AS fm
INNER JOIN V_ShopList AS sl
ON sl.shopcode=fm.tif_shopCode
WHERE 1=1 
AND fm.tif_progress = 1 
AND sl.CompanyID='KM'
AND fm.tif_checkDate>='{0}'
AND fm.tif_checkDate<='{1}' 
{2}
GROUP BY sl.AreaName 
ORDER BY ave DESC ", StartTime, EndTime, BigAreaName);

            DataSet ds = mydb.DM_GetDateSet(selQuery);
            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    //获取店经理平均分
    public DataTable ManagerAve(DateTime StartTime, DateTime EndTime, string[] ShopManager)
    {
        try
        {
            string ManagerName = string.Empty;
            string ShopManagerItem = StrSplit(ShopManager);

            if (!string.IsNullOrEmpty(ShopManagerItem))
            {
                ManagerName = string.Format(@" AND fm.tif_shopManager IN {0} ", ShopManagerItem);
            }
            string selQuery = string.Format(@"SELECT fm.tif_shopManager AS name,COUNT(1) AS num,cast(SUM(fm.new_tif_score)/COUNT(1) AS NUMERIC(10,1)) AS ave FROM V_t_inspection_finishMaster AS fm
WHERE 1=1 
AND fm.tif_progress = 1 
AND fm.tif_checkDate>='{0}'
AND fm.tif_checkDate<='{1}'
{2}
GROUP BY fm.tif_shopManager 
ORDER BY ave DESC ", StartTime, EndTime, ManagerName);

            DataSet ds = mydb.DM_GetDateSet(selQuery);
            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    //获取省份平均分
    public DataTable MapAve(DateTime StartTime, DateTime EndTime)
    {
        try
        {
            string selQuery = string.Format(@"SELECT sl.BigAreaName AS name,COUNT(1) AS num,cast(SUM(fm.new_tif_score)/COUNT(1) AS NUMERIC(10,1)) AS ave FROM V_t_inspection_finishMaster AS fm
INNER JOIN V_ShopList AS sl
ON sl.shopcode=fm.tif_shopCode
WHERE 1=1 
AND fm.tif_progress = 1 
AND sl.CompanyID='KM'
AND fm.tif_checkDate>='{0}'
AND fm.tif_checkDate<='{1}'
GROUP BY sl.BigAreaName ", StartTime, EndTime);

            DataSet ds = mydb.DM_GetDateSet(selQuery);
            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    //查询请求参数实体类
    public class QueryReqModel
    {
        /// <summary>
        /// 查询开始时间
        /// </summary>
        public DateTime StartTime { get; set; }
        /// <summary>
        /// 查询结束时间
        /// </summary>
        public DateTime EndTime { get; set; }
        /// <summary>
        /// 省份
        /// </summary>
        public string Province { get; set; }
        /// <summary>
        /// 店经理
        /// </summary>
        public string[] ShopManager { get; set; }
    }

    //查询按钮响应的所有数据实体类
    public class QueryResDataModel
    {
        /// <summary>
        /// 响应状态
        /// </summary>
        public int State { get; set; }
        /// <summary>
        /// 全国平均分
        /// </summary>
        public decimal AllAve { get; set; }
        /// <summary>
        /// 城市平均数
        /// </summary>
        public DataTable CityAve { get; set; }
        /// <summary>
        /// 店经理平均数
        /// </summary>
        public DataTable ManagerAve { get; set; }
        /// <summary>
        /// 省份平均数
        /// </summary>
        public DataTable MapAve { get; set; }
    }

    //导出时间响应的实体类
    public class OutExcelPath
    {
        /// <summary>
        /// 响应状态 0:失败 1:成功
        /// </summary>
        public int State { get; set; }
        /// <summary>
        /// 错误消息描述
        /// </summary>
        public string Msg { get; set; }
        /// <summary>
        /// 导出Excel的下载地址
        /// </summary>
        public string Path { get; set; }
    }

}